package Sunjuk;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import oracle.jdbc.proxy.annotation.Pre;

public class EX01_SungJukDAO implements EX01_ISungJukDAO{
	
	private String DRV = "oracle.jdbc.driver.OracleDriver";
	private String URL = "jdbc:oracle:thin:koby99/koby99@112.216.253.125:1521:xe";
	private String insertSQL = "insert into SUNGJUK values ('kor' || to_char(exam.NEXTVAL), ?, ?, ?, ?, ?, ?, ?)";
	private String SelectSQL = "select * from SUNGJUK order by sjno desc";
	
	@Override
	public boolean insertSungJuk(EX01_SungJukVO s, EX01_SungJukService ss) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		boolean result = false;
		
		try{
			conn = this.openDatebase();
			pstmt = conn.prepareStatement(insertSQL);
				pstmt.setString(1, s.getName() );
				pstmt.setInt(2, s.getKor() );
				pstmt.setInt(3, s.getEng() );
				pstmt.setInt(4, s.getMat() );
				pstmt.setInt(5, ss.tot(s) );
				pstmt.setFloat(6, (float)ss.avg(s) );
				pstmt.setString(7, ss.grd(s) );
			pstmt.executeUpdate();
			result = true;
		}catch(Exception ex){
			ex.printStackTrace();
		}finally{ close(conn, pstmt, null);
		}
		
		return result;
	}
	
	private Connection openDatebase(){
		try{Class.forName(DRV);
		return DriverManager.getConnection(URL);
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return null;
	}
	
	private void close(
			Connection c, PreparedStatement p, ResultSet r){
		if (r != null) try { r.close(); r= null; } catch(Exception e) {}
		if ( p != null) try {p.close(); p= null;} catch(Exception e){}
		if ( c != null) try {c.close(); c= null;} catch(Exception e){}
		
	}
	
	@Override
	public List<EX01_SungJukVO> selectSungJuk() {
		Connection conn =null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		List<EX01_SungJukVO> result = new ArrayList<>();
		try{
			conn = openDatebase();
			pstmt = conn.prepareStatement(SelectSQL);
			rs = pstmt.executeQuery();
			while(rs.next()){
				EX01_SungJukVO s = new EX01_SungJukVO();
				s.setSjno(rs.getString("sjno"));
				s.setName(rs.getString("name"));
				s.setKor(rs.getInt("kor"));
				s.setEng(rs.getInt("eng"));
				s.setMat(rs.getInt("mat"));
				s.setTot(rs.getInt("tot"));
				s.setAvg(rs.getFloat("avg"));
				s.setGrd(rs.getString("grd"));
				result.add(s);
			}
		}catch (Exception e) {
			e.printStackTrace();
		}finally{
			close(conn, pstmt, rs);
		}
		return result;
	} 
}
